import sys
import os

# 获取当前脚本所在目录
current_dir = os.path.dirname(os.path.abspath(__file__))
# 切换到项目根目录
project_root = os.path.abspath(os.path.join(current_dir, '..'))
sys.path.insert(0, project_root)

from datetime import datetime
import random
import pandas as pd
from DataBase import DataBase


class Insert:
    def __init__(self, dir_, file_, sep):
        self.dir = dir_
        self.file = file_
        self.df = pd.read_csv(f"{dir_}/{file_}") if "xlsx" in file_ else pd.read_csv(f"{dir_}/{file_}", sep=sep,
                                                                                     header=None)
        self.db = DataBase()

    def insertProduct(self):
        data = []
        for tup in zip(self.df[0], self.df[1], self.df[2], self.df[3], self.df[4], self.df[5], self.df[6]):
            data.append((int(tup[0]), *tup[1:]))
        sql = """
           insert into product(product_id, product_name, product_category_id, amazon_id, product_image, product_category, product_ugc_tag)
           values (%s,%s,%s,%s,%s,%s,%s)
        """
        self.db.insert_many(sql, data)

    def insertRating(self):
        data = []
        for tup in zip(self.df[0], self.df[1], self.df[2], self.df[3]):
            user_id, product_id, rating, timestamp = tup
            user_id, product_id = int(user_id), int(product_id)
            rating = float(rating)
            timestamp = datetime.utcfromtimestamp(timestamp)
            data.append((user_id, product_id, rating, timestamp))
        sql = """
           insert into ratings(user_id, product_id, rating, rating_time)
           values (%s,%s,%s,%s)
        """
        self.db.insert_many(sql, data)

    def insertUser(self):
        data = []
        user_id_set = set()
        for tup in zip(self.df[0], self.df[1], self.df[2], self.df[3]):
            user_id, product_id, rating, timestamp = tup
            if user_id not in user_id_set:
                user_id = int(user_id)
                user_id_set.add(user_id)
            else:
                continue
            account = random.randint(00000000, 99999999)
            password = "20e5fb3bd575db7c09a0c0d9ad814d3b"
            email = "tao_xin_li@163.com"
            data.append((user_id, account, password, email))
        sql = """
           insert into user(user_id, account, password, email)
           values (%s,%s,%s, %s)
        """
        self.db.insert_many(sql, data)


if __name__ == '__main__':
    path = "static/file"
    filename = "ratings.csv"
    insertEntity = Insert(path, filename, ',')
    insertEntity.insertRating()
    print("rating finished!")

    filename = "ratings.csv"
    insertEntity = Insert(path, filename, ',')
    insertEntity.insertUser()
    print("user finished!")

    filename = "products.csv"
    insertEntity = Insert(path, filename, '^')
    insertEntity.insertProduct()
    print("product finished!")
